package com.cindata

import com.cindata.dictionary.*
import com.cindata.base.*

import grails.gorm.transactions.Transactional
import org.apache.commons.lang.StringUtils
import org.apache.commons.lang.time.DateFormatUtils
import org.joda.time.DateTimeUtils

import java.text.SimpleDateFormat
import java.util.concurrent.ExecutorService
import java.util.concurrent.Executors

@Transactional
class EvaluateService
{
    static scope = "singleton"
    private static int batchLimit = 5000
    def apiService
    //def evaluationApiService
    def splitMatchApiService
    def dataSource
    def logService

    def threadCount = 0
    def finishThreadCount = 0

    def sessionFactory

    def rootName = EvaluateService.class.getResource("/").getPath();
    Map<String, BigDecimal> progressMap = new HashMap<String, BigDecimal>(100);
    private ExecutorService evaluatePool = Executors.newFixedThreadPool(3)
    List<Map> failedList = new ArrayList<Map>();

    def batchInserts(List<Map> paramMapList, EstateImportBatch batch, Date currentDate)
    {
        setProgress(batch.id, BigDecimal.valueOf(0))
       
        if (batch.importFileType.code == 'Estate')
        {
            estateInserts(paramMapList, batch, currentDate)
        }
        else if (batch.importFileType.code == 'Repayment')
        {
            repaymentInserts(paramMapList, batch, currentDate)
        }

        setProgress(batch.id, BigDecimal.valueOf(100))
    }

    def batchInsertsVehicle(List<Map> paramMapList, VehicleImportBatch batch, Date currentDate)
    {
        setProgress(batch.id, BigDecimal.valueOf(0))
        if (batch.importFileType.code == 'Estate')
        {
            vehicleInserts(paramMapList, batch, currentDate)
        }
        else if (batch.importFileType.code == 'Repayment')
        {
            repaymentInserts(paramMapList, batch, currentDate)
        }
        setProgress(batch.id, BigDecimal.valueOf(100))
    }

    //    押品数据导入
    def estateInserts(List<Map> estateMapList, EstateImportBatch batch, Date currentDate)
    {
        def i = 0
        def count = estateMapList.size()
        def createBy = batch.createBy
        def BuildingTypeDictArr = BuildingTypeDict.all
        def EstateStatusArr = EstateStatusDict.all
        def HouseTypeArr = HouseTypeDict.all
        def RepaymentModeArr = RepaymentModeDict.all
        def ForwardLoanStepArr = ForwardLoanStepDict.all
        def LoanMethodArr = LoanMethodDict.all
        def AccountArr = Account.all
        def GuaranteeMethodArr = GuaranteeMethodDict.all
        def AccountNatureArr = AccountNatureDict.all
        def OrganizationNatureArr = OrganizationNatureDict.all
        def DepositedStateArr = DepositedStateDict.all
        def BuildingTypeArr = BuildingTypeDict.all
        def CityArr = City.all
        def DistrictArr = District.all
        def defaultBuildingType = BuildingTypeArr.find {
            it.code == "Residential"
        }
        def evaluateStatue = ExecStatusDict.findByCode('2')
        estateMapList.each { Map param ->
            param.batch = batch
            param.createdDate = currentDate
            param.createBy = createBy
            param.city = param.cityName ? CityArr.find { it.name == param.cityName } : null
            param.district = param.districtName ? DistrictArr.find {
                it.name == param.districtName && it.parentAreaId == param?.city.id
            } : null
            param.estateStatus = param.estateStatus ? EstateStatusArr.find { it.name == param.estateStatus } : null
            param.estateType = param.estateType ? HouseTypeArr.find { it.name == param.estateType } : null
            param.isFirstHouse = (param.isFirstHouse == '是' ? true : false)
            param.starRepaymentMode = param.starRepaymentMode ? RepaymentModeArr.find {
                it.name == param.starRepaymentMode
            } : null
            param.forwardLoanStep = param.forwardLoanStep ? ForwardLoanStepArr.find {
                it.name == param.forwardLoanStep
            } : null
            param.loanMethod = param.loanMethod ? LoanMethodArr.find { it.name == param.loanMethod } : null
            param.curRepaymentMode = param.curRepaymentMode ? RepaymentModeArr.find {
                it.name == param.curRepaymentMode
            } : null
            param.guaranteeInstitution = param.guaranteeInstitution ? AccountArr.find {
                it.name == param.guaranteeInstitution
            } : null
            param.guaranteeMethod = param.guaranteeMethod ? GuaranteeMethodArr.find {
                it.name == param.guaranteeMethod
            } : null
            param.borrAccountNature = param.borrAccountNature ? AccountNatureArr.find {
                it.name == param.borrAccountNature
            } : null
            param.borrWorkUnitNature = param.borrWorkUnitNature ? OrganizationNatureArr.find {
                it.name == param.borrWorkUnitNature
            } : null
            param.borrDepositedState = param.borrDepositedState ? DepositedStateArr.find {
                it.name == param.borrDepositedState
            } : null
            param.buildingType = param.buildingType ? BuildingTypeDictArr.find {
                it.name == param.buildingType
            } : defaultBuildingType
            param.evaluateStatue = evaluateStatue
            param.account = createBy?.account
            param.province = param.city?.parentArea

            //实例化并将读取的单元格参数给其赋值
            def estateImport
            try
            {
                estateImport = new EstateImport(param)
                if (estateImport.validate()) 
                {
                    estateImport.save(flush:true)
                }
                else
                {
                    println estateImport.errors
                }
                i++
                if (i % 1000 == 0)
                {
                    def progress = i / count * 100
                    setProgress(batch.id, BigDecimal.valueOf(progress))
                    sessionFactory.currentSession.flush()
                    sessionFactory.currentSession.clear()
                }
            }
            catch (Exception e)
            {
                println "EstateImport not saved, errors = ${estateImport?.errors}"
                log.error(e)
            }
        }
    }

    //    还款数据导入
    def repaymentInserts(List<Map> repaymentMapList, EstateImportBatch batch, Date currentDate)
    {
        def i = 0
        def count = repaymentMapList.size()
        def createBy = batch.createBy
        def RepaymentTypeArr = RepaymentTypeDict.all
        def OverdueStateArr = OverdueStateDict.all
        def PrepaymentTypeArr = PrepaymentTypeDict.all
        repaymentMapList.each { Map param ->
            param.batch = batch
            param.createdDate = currentDate
            param.createBy = createBy
            param.modifyBy = createBy
            param.repaymentType = param.repaymentType ? RepaymentTypeArr.find { it.name == param.repaymentType } : null
            param.overdueState = param.overdueState ? OverdueStateArr.find { it.name == param.overdueState } : null
            param.prepaymentType = param.prepaymentType ? PrepaymentTypeArr.find {
                it.name == param.prepaymentType
            } : null
            def repaymentImport
            try
            {
                repaymentImport = new RepaymentImport(param)
                repaymentImport.save(flush:true)
                i++
                if (i % 1000 == 0)
                {
                    def progress = i / count * 100
                    setProgress(batch.id, BigDecimal.valueOf(progress))
                    sessionFactory.currentSession.flush()
                    sessionFactory.currentSession.clear()
                }
            }
            catch (Exception e)
            {
                println "RepaymentImport not saved, errors = ${repaymentImport?.errors}"
                log.error(e)
            }
        }
    }

    //    车辆押品数据导入
    def vehicleInserts(List<Map> vehicleMapList, VehicleImportBatch batch, Date currentDate)
    {
        def i = 0
        def count = vehicleMapList.size()
        println "vehicleInserts  matchNum:   " + batch.matchNum

        def CityArr = City.all
        def DistrictArr = District.all
        def EstateStatusArr = EstateStatusDict.all
        def IdentityTypeArr = ContactIdentityType.all
        def CurrencyArr = CurrencyDict.all
        def CashabilityArr = Cashability.all
        def TypeArr = VehicleTypeDict.all
        def evaluateStatueArr = ExecStatusDict.all
        def createBy = batch.createBy

        vehicleMapList.each { Map param ->
            //遍历map集合
            param.batch = batch
            param.createdDate = currentDate
            param.createBy = createBy
            param.city = param.city ? CityArr.find { it.name == param.city } : null
            param.district = param.district ? DistrictArr.find { it.name == param.district && it.parentAreaId == param?.city.id } : null
            param.estateStatus = param.estateStatus ? EstateStatusArr.find { it.name == param.estateStatus } : null
            param.identityType = param.identityType ? IdentityTypeArr.find { it.name == param.identityType } : null
            param.cashability = param.cashability ? CashabilityArr.find { it.name == param.cashability } : null
            param.type = param.type ? TypeArr.find { it.name == param.type } : null
            param.currency = param.currency ? CurrencyArr.find { it.name == param.currency } : null
            param.account = param.account ? Account.findByName(param.account) : null
            param.evaluateStatue = param.evaluateStatue ? evaluateStatueArr.find {
                it.name == param.evaluateStatue
            } : null

            def vehicleImport
            try
            {
                vehicleImport = new VehicleImport(param)
                vehicleImport.save(flush:true)
                i++
                if (i % 1000 == 0)
                {
                    def progress = i / count * 100
                    setProgress(batch.id, BigDecimal.valueOf(progress))
                    sessionFactory.currentSession.flush()
                    sessionFactory.currentSession.clear()
                }
            }
            catch (Exception e)
            {
                println "VehicleImport not saved, errors = ${vehicleImport?.errors}"
                log.error(e)
            }

        }

    }

    //    应收账款数据导入
    def accountsReceivableInserts(List<Map> estateMapList, AccountsReceivableImportBatch batch, Date currentDate)
    {

    }

    //    金融质押数据导入
    def financialPledgeInserts(List<Map> estateMapList, FinancialPledgeImportBatch batch, Date currentDate)
    {
        setProgress(batch.id, BigDecimal.valueOf(0))
        def i = 0
        def count = estateMapList.size()

        estateMapList.each { Map param ->
            //遍历map集合
            param.province = param.province ? Province.findByName(param.province) : null
            param.city = param.city ? City.findByName(param.city) : null
            param.district = param.district && param.city ? District.findByNameAndParentArea(param.district, param.city) : null
            param.estateStatus = param.estateStatus ? EstateStatusDict.findByName(param.estateStatus) : null
            param.isEstate = (param.isEstate == '是' ? true : false)
            param.account = param.account ? Account.findByName(param.account) : null
            param.type = param.type ? FinancialPledgeTypeDict.findByName(param.type) : null
            param.identityType = param.identityType ? ContactIdentityType.findByName(param.identityType) : null
            param.isValidate = (param.isValidate == '已审核' ? true : false)
            param.currency = param.currency ? CurrencyDict.findByName(param.currency) : null
            param.cashability = param.cashability ? Cashability.findByName(param.cashability) : null
            param.isNativeDeposit = (param.isNativeDeposit == '是' ? true : false)
            param.isElectronicDeposit = (param.isElectronicDeposit == '是' ? true : false)
            param.isOrienteeringBond = (param.isOrienteeringBond == '是' ? true : false)
            param.bondAmount
            param.isRegisteredBond = (param.isRegisteredBond == '是' ? true : false)
            param.isNeedStopPayment = (param.isNeedStopPayment == '是' ? true : false)
            param.isCapitalProtected = (param.isCapitalProtected == '是' ? true : false)
            param.isRedemption = (param.isRedemption == '是' ? true : false)
            param.isHighQualityAccount = (param.isHighQualityAccount == '是' ? true : false)
            param.isHavePledgeClause = (param.isHavePledgeClause == '是' ? true : false)
            param.isAgreedPledgeRate = (param.isAgreedPledgeRate == '是' ? true : false)
            param.batch = batch
            param.createdDate = currentDate
            param.createBy = batch.createBy

            def estateImport
            //实例化并将读取的单元格参数给其赋值
            try
            {
                estateImport = new FinancialPledgeImport(param)
                if (estateImport.validate())
                {
                    estateImport.save(flush:true)
                }
                else
                {
                    println estateImport.errors
                }
                i++
                if (i % 1000 == 0)
                {
                    def progress = i / count * 100
                    setProgress(batch.id, BigDecimal.valueOf(progress))
                    sessionFactory.currentSession.flush()
                    sessionFactory.currentSession.clear()
                }
            }
            catch (Exception e)
            {
                println "FinancialPledgeImport not saved, errors = ${estateImport?.errors}"
                log.error(e)
            }

        }
        setProgress(batch.id, BigDecimal.valueOf(100))
    }

    //    其他押品数据导入
    def othersCollateralInserts(List<Map> estateMapList, OtherCollateralImportBatch batch, Date currentDate)
    {

    }

    //    押品数据导入
    def contactInserts(List<Map> contactMapList, ContactImportBatch batch, Date currentDate)
    {
        setProgress(batch.id, BigDecimal.valueOf(0))
        def i = 0
        def count = contactMapList.size()
        def createBy = batch.createBy
        def ContactIdentityTypeArr = ContactIdentityType.all

        contactMapList.each { Map param ->
            //遍历map集合
            param.batch = batch
            param.identityType = param.identityType ? ContactIdentityTypeArr.find {it.name == param.identityType} : null
            param.createdDate = currentDate
            param.createBy = createBy

            def contactImport
            //实例化并将读取的单元格参数给其赋值
            try
            {
                contactImport = new ContactImport(param)
                contactImport.save flush:true
                if(validateParam(contactImport))
                {
                    contactImport.evaluateStatue = ExecStatusDict.findByName("成功")
                    contactImport.validSuccess()
                }else{
                    contactImport.evaluateStatue = ExecStatusDict.findByName("失败")
                    contactImport.validFailure()
                }
                contactImport.save flush:true
                i++
                if (i % 1000 == 0)
                {
                    def progress = i / count * 100
                    setProgress(batch.id, BigDecimal.valueOf(progress))
                    sessionFactory.currentSession.flush()
                    sessionFactory.currentSession.clear()
                }
            }
            catch (Exception e)
            {
                println "ContactImport not saved, errors = ${contactImport?.errors}"
                log.error(e)
            }

        }
        setProgress(batch.id, BigDecimal.valueOf(100))
    }

    def validateParam(ContactImport contactImport)
    {
        def contact = Contact.findByIdNumber(contactImport.idNumber)
        if(contact){
            contactImport.reason = "借款人已经存在"
            return false
        }
        if(contactImport?.idNumber && contactImport?.idNumber.length() != 18){
            contactImport.reason = "身份证长度校验失败"
            return false
        }
        return true
    }

    /**
     * 设置执行进度*/
    def setProgress(Long progressKey, BigDecimal progress)
    {
        ProgressUtil.setProgress(progressKey, progress)
    }

    def getProgress(Long progressKey)
    {
        return ProgressUtil.progressMap.get(progressKey)
    }

    /**
     *多线程评估
     * @param batchId
     * @param em
     */
    def threadEvaluate(List<EstateImport> list, def em, def importBatch, def count)
    {
        def sql = new groovy.sql.Sql(dataSource)
        int excuteCounts = 0

        def SUCCESS = ExecStatusDict.findByCode('1')
        def FAILURE = ExecStatusDict.findByCode('-1')
        def EXECUTING = ExecStatusDict.findByCode('2')

        def AUTOMATIC = EvaluateDict.findByCode('1')
        def ARTIFICIAL = EvaluateDict.findByCode('2')
        // List<Map>  failedList = new ArrayList<Map>()
        list?.each 
        {
            def bean = EstateImport.get(it?.id)
            def param = [em             : em,
                         batchNum       : importBatch.id,
                         batchTotalCount: count,
                         batchLevel     : '户',
                         city           : bean.getCityName(),
                         address        : bean.getAddress(),
                         floor          : bean.getCurrentFloor(),
                         totalFloor     : bean.getTotalFloor(),
                         square         : bean.getBuildArea()]

            excuteCounts++
            println "正在执行第 " + excuteCounts + " 条记录"
            def result = [:]
            def beanMap = []
            try 
            {
                // 自动评估
                if (param.em == 1) 
                {
                    println "startTime:" + new Date()
                    def addressSplitList = splitMatchApiService.splitMatch(bean?.city?.name, bean?.address)
                    def communityId = addressSplitList?.match_community_id
                    if (communityId && communityId != "-1") {
                        beanMap.add(addressSplitList?.match_community_id?.toInteger())
                        beanMap.add(addressSplitList?.match_community_name)
                        beanMap.add(addressSplitList?.match_building_id?.toInteger())
                        beanMap.add(addressSplitList?.match_building_name)
                        beanMap.add(addressSplitList?.match_unit_id?.toInteger())
                        beanMap.add(addressSplitList?.match_unit_name)
                        beanMap.add(addressSplitList?.match_house_id?.toInteger())
                        beanMap.add(addressSplitList?.match_house_name)
                        beanMap.add(addressSplitList?.match_street_id?.toInteger())
                        beanMap.add(addressSplitList?.match_street_name)
                        beanMap.add(addressSplitList?.minx?.toDouble())
                        beanMap.add(addressSplitList?.miny?.toDouble())

                        param['name'] = addressSplitList?.match_community_name
                        result = apiService.evaluate(param)
                    } else {
                        result['code'] = "-1"
                        result['message'] = "地址拆分失败"
                    }
                    println "endTime: " + new Date()
                }

                // 人工评估
                if (param.em == 7) {
                    result = apiService.evaluate(param)
                }
                // 自动评估和人工
                if (param.em == 8) 
                {
                    def addressSplitList = splitMatchApiService.splitMatch(bean?.city?.name, bean?.address)
                    def communityId = addressSplitList?.match_community_id
                    if (communityId && communityId != "-1") 
                    {
                        beanMap.add(addressSplitList?.match_community_id?.toInteger())
                        beanMap.add(addressSplitList?.match_community_name)
                        beanMap.add(addressSplitList?.match_building_id?.toInteger())
                        beanMap.add(addressSplitList?.match_building_name)
                        beanMap.add(addressSplitList?.match_unit_id?.toInteger())
                        beanMap.add(addressSplitList?.match_unit_name)
                        beanMap.add(addressSplitList?.match_house_id?.toInteger())
                        beanMap.add(addressSplitList?.match_house_name)
                        beanMap.add(addressSplitList?.match_street_id?.toInteger())
                        beanMap.add(addressSplitList?.match_street_name)
                        beanMap.add(addressSplitList?.minx?.toDouble())
                        beanMap.add(addressSplitList?.miny?.toDouble())

                        param['em'] = 1
                        param['name'] = addressSplitList?.match_community_name
                        result = apiService.evaluate(param)
                        if (result && result?.code != "0") 
                        {
                            param['beanId'] = bean?.id
                            synchronized (failedList)
                            {
                                failedList.add(param)
                            }
                            param['em'] = 9
                        }
                    } 
                    else 
                    {
                        param['beanId'] = bean?.id
                        synchronized (failedList)
                        {
                            failedList.add(param)
                        }
                        result['code'] = "-1"
                        result['message'] = "地址拆分失败"
                    }
                }

                // 发起远程评估以后，要产进行计费
                // UserCost.CreateByEstateEvaluate(importBatch.id, bean)
                if (result) 
                {
                    println "evaluate result:" + result
                    if (param.em == 1) {
                        if ("0".equals(result?.code)) 
                        {
                            // 自动评估成功
                            beanMap.add(result?.unitprice)
                            beanMap.add(result?.saleprice)
                            beanMap.add(result?.evaluateCode)
                            beanMap.add(SUCCESS?.id)
                            beanMap.add(AUTOMATIC?.id)
                            def cltv = null
                            if (bean.loansBalance && result?.saleprice) {
                                cltv = bean.loansBalance / result?.saleprice
                            }
                            beanMap.add(cltv)
                            beanMap.add(bean?.id)

                            sql.execute "update estate_import set community_id=?,community_name=?,building_id=?,building_name=?,unit_id=?,unit_name=?,house_id=?,house_name=?,street_id=?,street_name=?,x=?,y=?,unit_price=?,total_price=?,evaluate_code=?,evaluate_statue_id=?,evaluate_type_id=?,evaluate_date=to_timestamp('${result?.evaluateDate}','YYYY-MM-DD HH24:MI:SS.MS'),latest_evaluate_date=to_timestamp('${result?.evaluateDate}','YYYY-MM-DD HH24:MI:SS.MS'),cltv = ? where id=?", beanMap
                            
                            // 成功计费
                            // bean.evaluteSuccess()
                        } 
                        else if ("-1".equals(result?.code))
                        {
                            println "--------------------"
                            //自动评估拆分失败
                            beanMap.add(FAILURE?.id)
                            beanMap.add(result?.message)
                            beanMap.add(bean?.id)

                            sql.execute "update estate_import set evaluate_statue_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap

                            // 失败计费
                            // bean.evaluteFailure()
                        }
                        else
                        {
                            //自动评估失败
                            println "自动评估 失败，serialNumber:" + bean?.serialNumber + " code:" + result?.evaluateCode + " message:" + result?.message
                            beanMap.add(result?.unitprice)
                            beanMap.add(result?.saleprice)
                            beanMap.add(result?.evaluateCode)
                            beanMap.add(FAILURE?.id)
                            beanMap.add(AUTOMATIC?.id)
                            beanMap.add(result?.message)
                            beanMap.add(bean?.id)
                            sql.execute "update estate_import set community_id=?,community_name=?,building_id=?,building_name=?,unit_id=?,unit_name=?,house_id=?,house_name=?,street_id=?,street_name=?,x=?,y=?,unit_price=?,total_price=?,evaluate_code=?,evaluate_statue_id=?,evaluate_type_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap

                            // 失败计费
                            // bean.evaluteFailure()
                        }
                    } 
                    else if (param.em == 7) 
                    {
                        if ("4".equals(result?.code)) 
                        {
                            // 转人工评估成功
                            beanMap.add(result?.evaluateCode)
                            beanMap.add(EXECUTING?.id)
                            beanMap.add(ARTIFICIAL?.id)
                            beanMap.add(bean?.id)
                            sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,evaluate_type_id=?,modified_date=CURRENT_TIMESTAMP where id=?", beanMap
                        } 
                        else 
                        {
                            //转人工评估失败
                            beanMap.add(result?.evaluateCode)
                            beanMap.add(FAILURE?.id)
                            beanMap.add(result?.message)
                            beanMap.add(bean?.id)
                            sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap

                            // bean.evaluteFailure()
                        }
                    } 
                    // else if (param.em == 8) 
                    // {
                    //     if ("-1".equals(result?.code)) 
                    //     {
                    //         // 自动加人工评估地址拆分失败
                    //         beanMap.add(FAILURE?.id)
                    //         beanMap.add(result?.message)
                    //         beanMap.add(bean?.id)

                    //         sql.execute "update estate_import set evaluate_statue_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap
                    //     }
                    // }
                }
                else
                {
                    logService.error(rootName, bean?.batch?.id.toString(), "没有返回结果，serialNumber:" + bean?.serialNumber)
                }
            }
            catch (Exception ex) 
            {
                ex.printStackTrace()
                def beanMap1 = []
                beanMap1.add("9999")
                beanMap1.add(FAILURE?.id)
                beanMap1.add("远程API调用失败:${ex.getMessage()}")
                beanMap1.add(bean?.id)
                sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap1

                // bean.evaluteFailure()
                log.error("9999远程API调用失败")
            }
        }
        sql.close()
    }

    def addsynThreadCount()
    {
        synchronized (finishThreadCount)
        {
            finishThreadCount ++
        }
    }

    /**
     * 批次评估*/
    synchronized void evaluateBatch(Serializable batchId, Integer em)
    {
        EstateImportBatch importBatch = EstateImportBatch.get(batchId)
        int count = EstateImport.countByBatch(importBatch)
        if (count > 0)
        {
            int batchCounts = (count / 5) + 1
            for (int i = 0; i < 5; i++)
            {
                threadCount  = threadCount + 1
                def estateQuery = EstateImport.where {
                    batch.id == importBatch.getId()
                }
                def list = estateQuery.list([sort: 'id', offset: i * batchCounts, max: batchCounts])
                def newList = []
                list.each {
                    newList.add(it)
                }
                Thread.start 
                {
                    try 
                    {
                        EstateImport.withTransaction
                        {
                            threadEvaluate(newList, em, importBatch, count)
                        }    
                    }
                    catch(Exception e) 
                    {
                        println "${Thread.currentThread().getName()} 线程执行异常"
                        e.printStackTrace()
                    }
                    finally 
                    {
                        addsynThreadCount()
                    }
                }
            }

            while(true)
            {
                if (threadCount == finishThreadCount)
                {
                    try 
                    {
                        if (failedList?.size() > 0)
                        {
                            failedBeanBatchEvaluate(failedList)
                        }
                    }
                    catch(Exception e) 
                    {
                        println "失败押品异常！"
                        e.printStackTrace()
                    }
                    finally 
                    {
                        threadCount = 0
                        finishThreadCount = 0
                        failedList.clear()
                    }
                    break
                }
                println "threadCount:" + threadCount + " finishThreadCount:" + finishThreadCount
                Thread.sleep(1000)
            }
        }
    }

    // 自动加人工评估：失败押品转人工评估
    def failedBeanBatchEvaluate(List<Map> failedList)
    {
        def sql = new groovy.sql.Sql(dataSource)
        def FAILURE = ExecStatusDict.findByCode('-1')
        def EXECUTING = ExecStatusDict.findByCode('2')
        def ARTIFICIAL = EvaluateDict.findByCode('2')

        def excuteCounts = 0

        int count = failedList?.size()
        failedList?.each 
        {
            excuteCounts++
            println "失败 ${count} 条，正在执行第 " + excuteCounts + " 条转人工"
            it['em'] = 7
            it['batchTotalCount'] = count
            def beanMap = []
            def result = [:]
            try
            {
                println "人工评估参数:" + it
                result = apiService.evaluate(it)
                if (result)
                {
                    println "result:" + result?.evaluateCode
                    if ("4".equals(result?.code))
                    {
                        // 转人工评估成功
                        beanMap.add(result?.evaluateCode)
                        beanMap.add(EXECUTING?.id)
                        beanMap.add(ARTIFICIAL?.id)
                        beanMap.add(it?.beanId)
                        sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,evaluate_type_id=?,modified_date=CURRENT_TIMESTAMP where id=?", beanMap
                    }
                    else
                    {
                        //转人工评估失败
                        beanMap.add(result?.evaluateCode)
                        beanMap.add(FAILURE?.id)
                        beanMap.add(result?.message)
                        beanMap.add(it?.beanId)
                        sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap

                        // bean.evaluteFailure()
                        log.error(result?.code + "" + result?.message)
                    }
                }
            }
            catch (Exception ex)
            {
                ex.printStackTrace()
                def beanMap1 = []
                beanMap1.add("9999")
                beanMap1.add(FAILURE?.id)
                beanMap1.add("远程API调用失败:${ex.getMessage()}")
                beanMap1.add(it?.beabId)
                sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap1

                // bean.evaluteFailure()
                log.error("9999远程API调用失败")
            }
        }
        sql.close()
    }

    // 自动评估：失败押品转人工评估
    def evaluateFailureEstate(Serializable batchId)
    {
        def FAILURE = ExecStatusDict.findByCode('-1')
        def EXECUTING = ExecStatusDict.findByCode('2')
        def ARTIFICIAL = EvaluateDict.findByCode('2')

        EstateImportBatch importBatch = EstateImportBatch.get(batchId)
        int count = EstateImport.countByBatchAndEvaluateStatue(importBatch, FAILURE)
        if (count > 0)
        {
            def sql = new groovy.sql.Sql(dataSource)
            for (int i = 0; i < (count + batchLimit - 1) / batchLimit; i++)
            {
                def estateQuery = EstateImport.where {
                    batch.id == importBatch.getId() && evaluateStatue.name == "失败"
                }
                def list = estateQuery.list([sort: 'id', offset: i * batchLimit, max: batchLimit])
                list?.each { EstateImport bean ->
                    def param = [em: 7,
                        batchNum: importBatch.id,
                        batchTotalCount: count,
                        batchLevel: '户',
                        city: bean.getCityName(),
                        address: bean.getAddress(),
                        floor: bean.getCurrentFloor(),
                        totalFloor: bean.getTotalFloor(),
                        square: bean.getBuildArea()]

                    def beanMap = []
                    def result = [:]
                    try
                    {
                        println "人工评估参数:" + param
                        result = apiService.evaluate(param)
                        if (result)
                        {
                            println "result:" + result
                            if ("4".equals(result?.code))
                            {
                                // 转人工评估成功
                                beanMap.add(result?.evaluateCode)
                                beanMap.add(EXECUTING?.id)
                                beanMap.add(ARTIFICIAL?.id)
                                beanMap.add(bean?.id)
                                sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,evaluate_type_id=?,modified_date=CURRENT_TIMESTAMP where id=?", beanMap
                            }
                            else
                            {
                                //转人工评估失败
                                beanMap.add(result?.evaluateCode)
                                beanMap.add(FAILURE?.id)
                                beanMap.add(ARTIFICIAL?.id)
                                beanMap.add(result?.message)
                                beanMap.add(bean?.id)
                                sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,evaluate_type_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap

                                // bean.evaluteFailure()
                                log.error(result?.code + "" + result?.message)
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ex.printStackTrace()
                        def beanMap1 = []
                        beanMap1.add("9999")
                        beanMap1.add(FAILURE?.id)
                        beanMap1.add("远程API调用失败:${ex.getMessage()}")
                        beanMap1.add(bean?.id)
                        sql.execute "update estate_import set evaluate_code=?,evaluate_statue_id=?,modified_date=CURRENT_TIMESTAMP,fail_reason=? where id=?", beanMap1

                        // bean.evaluteFailure()
                        log.error("9999远程API调用失败")
                    }
                }
            }
            sql.close()
        }
    }

    def updateBatchCount(EstateImportBatch estateImportBatchT)
    {
        def estateImportBatch = EstateImportBatch.findById(estateImportBatchT?.id)
        def SUCCESS = ExecStatusDict.findByCode('1')
        def FAILURE = ExecStatusDict.findByCode('-1')
        def PARTFAILURE = ExecStatusDict.findByCode('-2')

        def successCount = EstateImport.countByBatchAndEvaluateStatue(estateImportBatch, SUCCESS)
        def failureCount = EstateImport.countByBatchAndEvaluateStatue(estateImportBatch, FAILURE)
        estateImportBatch.matchNum = successCount + failureCount
        if (successCount + failureCount == estateImportBatch.estateNum)
        {
            if (failureCount == estateImportBatch.estateNum)
            {
                estateImportBatch.status = FAILURE
            }
            else if (failureCount > 0)
            {
                estateImportBatch.status = PARTFAILURE
            }
            else
            {
                estateImportBatch.status = SUCCESS
            }
        }
        estateImportBatch.save(flush:true)
    }

    def updateBatchRepaymentCount(EstateImportBatch estateImportBatchT)
    {
        def estateImportBatch  = EstateImportBatch.findById(estateImportBatchT?.id)
        def successCount = RepaymentImport.countByBatchAndExecStatus(estateImportBatch, ExecStatusDict.SUCCESS())
        def failureCount = RepaymentImport.countByBatchAndExecStatus(estateImportBatch, ExecStatusDict.FAILURE())

        estateImportBatch.matchNum = successCount + failureCount
        if (successCount + failureCount == estateImportBatch.estateNum)
        {
            if (successCount == estateImportBatch.estateNum)
            {
                estateImportBatch.status = ExecStatusDict.SUCCESS()
            }
            else if (failureCount == estateImportBatch.estateNum)
            {
                estateImportBatch.status = ExecStatusDict.FAILURE()
            }
            else if (failureCount > 0)
            {
                estateImportBatch.status = ExecStatusDict.PARTFAILURE()
            }
            else
            {
                estateImportBatch.status = ExecStatusDict.EXECUTING()
            }
        }
        estateImportBatch.modifiedDate = new Date()
        estateImportBatch.save(flush:true)
    }

    def bankAccountBatchImport(String webRootDir, File file, Serializable batchId)
    {
        def SUCCESS = ExecStatusDict.SUCCESS()
        def EVALUATE = EvaluateDict.EVALUATE()
        EstateImportBatch estateImportBatch = EstateImportBatch.findById(batchId)
        def batchNumber = estateImportBatch?.batchNumber
        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "GBK"))
        br.readLine()
        String line = null
        FileOutputStream fs = new FileOutputStream(new File(webRootDir + "logs/" + batchNumber + ".log"));
        PrintStream p = new PrintStream(fs);
        def st = 0
        def inquote = false
        def count = 0
        StringBuffer readLine = new StringBuffer()
        try
        {
            while ((line = br.readLine()) != null)
            {
                readLine.append(line)
                def maxPosition = readLine.length()
                while (st < maxPosition)
                {
                    def ch = readLine.charAt(st)
                    if (inquote && ch == ',')
                    {
                        readLine.setCharAt(st, (char) '，')
                    }
                    if (ch == '"')
                    {
                        inquote = !inquote
                    }
                    st++
                }

                line = readLine.toString()
                def splitByDoubleQuotes = line.split('"')
                def countDoubleQuotes = 0
                if (line.charAt(line.length() - 1) == '"')
                {
                    countDoubleQuotes = splitByDoubleQuotes.length
                }
                else
                {
                    countDoubleQuotes = splitByDoubleQuotes.length - 1
                }
                if (countDoubleQuotes % 2 == 1)
                {
                    continue
                }
                else
                {
                    readLine.setLength(0)
                    st = 0
                }

                def list = line.split(",")
                count++
                if (list)
                {
                    println list
                    def estate = Estate.findByExternalId(list[0])
                    def batchEstate = BatchEstate.findByEstateAndBatch(estate, estateImportBatch)
                    if (batchEstate) 
                    {
                        batchEstate.evaluateDate = Date.parse("yyyy/MM/dd", list[10])
                        batchEstate.evaluateStatue = SUCCESS
                        batchEstate.evaluateType = EVALUATE
                        if (batchEstate.validate()) 
                        {
                            batchEstate.save(flush:true)
                        }
                        else
                        {
                            println batchEstate.errors
                        }
                    }
                    
                    if (estate) 
                    {
                        estate.totalPrice = new BigDecimal(list[9])
                        estate.latestEvaluatePrice = new BigDecimal(list[9])
                        estate.valuationDate = Date.parse("yyyy/MM/dd", list[10])
                        estate.latestEvaluateDate = Date.parse("yyyy/MM/dd", list[10])
                        estate.latestEvaluateAccount = batchEstate.latestEvaluateAccount
                        if (estate.firstEvaluatePrice) 
                        {
                            estate.priceFluctuationsRatio = (estate.latestEvaluatePrice - estate.firstEvaluatePrice) / estate.firstEvaluatePrice
                        }
                        else
                        {
                            estate.firstEvaluatePrice = estate.latestEvaluatePrice
                        }
                        if (estate.validate()) 
                        {
                            estate.save(flush:true)
                        }
                        else
                        {
                            println estate.errors
                        }

                        ValuationHistory valuationHistory = new ValuationHistory()
                        valuationHistory.account = estate?.account
                        valuationHistory.name = estate?.communityName
                        valuationHistory.valuationDate = estate?.latestEvaluateDate
                        valuationHistory.unitPrice = estate?.unitPrice
                        valuationHistory.totalPrice = estate?.totalPrice
                        valuationHistory.sourceId = estate?.id
                        valuationHistory.sourceClass = "estate"
                        valuationHistory.evaluateStatus = SUCCESS
                        valuationHistory.evaluateType = EVALUATE
                        valuationHistory.save(flush:true)
                    }
                }
                else
                {
                    println "not list"
                }
            }

            // 修改批次表
            estateImportBatch.status = SUCCESS
            estateImportBatch.matchNum = count
            estateImportBatch.save(flush:true)
        }
        catch (Exception e)
        {
            log.info "file read wrong"
            p.println("file read wrong")
        }
        finally
        {
            if (br != null)
            {
                try
                {
                    br.close()
                    br = null
                }
                catch (IOException e)
                {
                    e.printStackTrace()
                }
            }
            log.info "file read success!!!"
            p.println("导入完成，本次导入订单总数: " + count)
            p.println("file read success!!!")
        }
        p.close()
    }

    def estateInsert(EstateImportBatch batch)
    {
        def evaluateStatue = ExecStatusDict.SUCCESS()

        def sql = new groovy.sql.Sql(dataSource)
        StringBuffer executeSql = new StringBuffer("select i.batch_id as e1,")
        executeSql.append(" i.external_id as e2,")
        executeSql.append(" i.latest_evaluate_account_id as e3,")
        executeSql.append(" i.province_id as e4," )
        executeSql.append(" i.city_id as e5,")
        executeSql.append(" i.district_id as e6,")
        executeSql.append(" i.estate_status_id as e7,")
        executeSql.append(" i.community_id as e8,")
        executeSql.append(" i.community_name as e9,")
        executeSql.append(" i.building_id as e10,")
        executeSql.append(" i.building_name as e11,")
        executeSql.append(" i.unit_id as e12,")
        executeSql.append(" i.unit_name as e13,")
        executeSql.append(" i.house_id as e14,")
        executeSql.append(" i.house_name as e15,")
        executeSql.append(" i.street_id as e16,")
        executeSql.append(" i.street_name as e17,")
        executeSql.append(" i.build_year as e18,")
        executeSql.append(" i.build_area as e19,")
        executeSql.append(" i.building_type_id as e20,")
        executeSql.append(" i.unit_price as e21,")
        executeSql.append(" i.total_price as e22,")
        executeSql.append(" i.ltv as e23,")
        executeSql.append(" i.cltv as e24,")
        executeSql.append(" i.address as e25,")
        executeSql.append(" i.build_area as e26,")
        executeSql.append(" i.total_floor as e27,")
        executeSql.append(" i.current_floor as e28,")
        executeSql.append(" i.loans_evaluate_total_price as e29,")
        executeSql.append(" i.loans_evaluate_price as e30,")
        executeSql.append(" i.loans_amount as e31,")
        executeSql.append(" i.loans_balance as e32,")
        executeSql.append(" i.start_date as e33,")
        executeSql.append(" i.end_date as e34,")
        executeSql.append(" i.evaluate_date as e35,")
        executeSql.append(" i.x as e36, " )
        executeSql.append(" i.y as e37," )
        executeSql.append(" i.loans_duration as e38," )
        executeSql.append(" i.remain_time as e39," )
        executeSql.append(" i.account_id as e40," )
        executeSql.append(" i.create_by_id as e41," )
        executeSql.append(" i.total_building_num as e42," )
        executeSql.append(" i.total_house_num as e43," )
        executeSql.append(" i.latest_evaluate_date as e44," )
        executeSql.append(" i.total_price as e45," )
        executeSql.append(" i.estate_type_id as e46" )
        executeSql.append(" from estate_import i where i.evaluate_statue_id = ${evaluateStatue?.id} and i.batch_id = ${batch?.id}")
        def result = sql.rows(executeSql?.toString())
        def estateSql = "insert into estate(id, version, batch_id, external_id, latest_evaluate_account_id, province_id, city_id, district_id, estate_status_id, community_id, community_name, building_id, building_name, unit_id, unit_name, house_id, house_name, street_id, street_name, build_year, build_area, building_type_id, "
            estateSql += " unit_price, total_price, ltv, cltv, source_address, source_build_area, source_total_floor, source_current_floor, loans_evaluate_total_price, loans_evaluate_price, loans_amount, loans_balance, start_date, end_date, valuation_date , x, y,"
            estateSql += " loans_duration, remain_time, account_id, create_by_id, total_building_num, total_house_num, latest_evaluate_date, latest_evaluate_price, is_estate, match_date,created_date,modified_date, estate_type_id)"
            estateSql += " values(nextval('hibernate_sequence'), 0, ?1.e1, ?1.e2, ?1.e3, ?1.e4, ?1.e5, ?1.e6, ?1.e7, ?1.e8, ?1.e9, ?1.e10, ?1.e11, ?1.e12, ?1.e13, ?1.e14, ?1.e15, ?1.e16, ?1.e17, ?1.e18, ?1.e19, ?1.e20, ?1.e21, ?1.e22, ?1.e23, ?1.e24, ?1.e25, ?1.e26, ?1.e27, ?1.e28, ?1.e29, ?1.e30, ?1.e31, ?1.e32, ?1.e33, ?1.e34, ?1.e35, ?1.e36, ?1.e37, ?1.e38, ?1.e39, ?1.e40, ?1.e41, ?1.e42, ?1.e43, ?1.e44, ?1.e45, true, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,?1.e46 ) "
            estateSql += " on conflict(external_id) do nothing"
        result?.each
        {
            try
            {
                sql.execute estateSql, it
            }
            catch(Exception e)
            {
                println "estateInsert error executeSql:" + executeSql
                logService.error(rootName,batch?.id,"estateInsert 失败，SQL:"+executeSql)
                e.printStackTrace()
            }
        }
        sql.close()
    }

    def collateralInserts(EstateImportBatch batch)
    {
        def type = CollateralType.findByCode("estate")
        def evaluateStatue = ExecStatusDict.SUCCESS()

        def sql = new groovy.sql.Sql(dataSource)
        StringBuffer executeSql = new StringBuffer("select e.name as c1,")
        executeSql.append(" e.external_id as c2, ")
        executeSql.append(" e.id as c3,")
        executeSql.append(" e.full_name as c4,")
        executeSql.append(" e.province_id as c5,")
        executeSql.append(" e.city_id as c6,")
        executeSql.append(" e.district_id as c7,")
        executeSql.append(" e.estate_status_id as c8,")
        executeSql.append(" e.is_estate as c9,")
        executeSql.append(" e.unit_price as c10,")
        executeSql.append(" e.total_price as c11,")
        executeSql.append(" e.valuation_date as c12,")
        executeSql.append(" e.ltv as c13,")
        executeSql.append(" e.cltv as c14,")
        executeSql.append(" e.account_id as c15,")
        executeSql.append(" e.identity_type_id as c16,")
        executeSql.append(" e.id_number as c17,")
        executeSql.append(" e.loan_card_number as c18,")
        executeSql.append(" e.common_owner_full_name as c19,")
        executeSql.append(" e.share_point as c20,")
        executeSql.append(" e.cashability_id as c21,")
        executeSql.append(" e.value_volatility as c22,")
        executeSql.append(" e.seizure_of_convenience as c23,")
        executeSql.append(" e.relation_of_mortgagor as c24,")
        executeSql.append(" e.legal_validity as c25,")
        executeSql.append(" e.first_evaluate_price as c26,")
        executeSql.append(" e.first_evaluate_account_id as c27,")
        executeSql.append(" e.latest_evaluate_price as c28,")
        executeSql.append(" e.latest_evaluate_account_id as c29,")
        executeSql.append(" e.latest_evaluate_date as c30,")
        executeSql.append(" e.next_evaluate_date as c31,")
        executeSql.append(" e.actual_mortgage_rate as c32,")
        executeSql.append(" e.mortgage_rate_upper_limit as c33,")
        executeSql.append(" e.register_full_name as c34,")
        executeSql.append(" e.register_account as c35,")
        executeSql.append(" e.register_date as c36,")
        executeSql.append(" e.register_update_date as c37,")
        executeSql.append(" e.create_by_id as c38")
        executeSql.append(" from estate_import i LEFT JOIN estate e on i.external_id = e.external_id where i.evaluate_statue_id = ${evaluateStatue?.id} and e.external_id is not null and i.batch_id = ${batch?.id}")
        def result = sql.rows(executeSql?.toString())
        def collateralSql = "insert into collateral(id, version, name, external_id, estate_id, full_name, province_id, city_id, district_id, estate_status_id, is_estate, unit_price, total_price, valuation_date, ltv, cltv, account_id, identity_type_id, "
            collateralSql += " id_number, loan_card_number, common_owner_full_name, share_point, cashability_id, value_volatility, seizure_of_convenience, relation_of_mortgagor, legal_validity, first_evaluate_price, first_evaluate_account_id, latest_evaluate_price,"
            collateralSql += " latest_evaluate_account_id, latest_evaluate_date, next_evaluate_date, actual_mortgage_rate, mortgage_rate_upper_limit, register_full_name, register_account, register_date, register_update_date, create_by_id, type_id, source_class, status,created_date,modified_date) "
            collateralSql += " values(nextval('hibernate_sequence'), 0, ?1.c1, ?1.c2, ?1.c3, ?1.c4, ?1.c5, ?1.c6, ?1.c7, ?1.c8, ?1.c9, ?1.c10, ?1.c11, ?1.c12, ?1.c13, ?1.c14, ?1.c15, ?1.c16, ?1.c17, ?1.c18, ?1.c19, ?1.c20, ?1.c21, ?1.c22, ?1.c23, ?1.c24, ?1.c25, ?1.c26, ?1.c27, ?1.c28, ?1.c29, ?1.c30, ?1.c31, ?1.c32, ?1.c33, ?1.c34, ?1.c35, ?1.c36, ?1.c37, ?1.c38, ${type?.id}, 'estate', 'Completed', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP) "
            collateralSql += " on conflict(estate_id) do nothing"
        result?.each
        {
            try 
            {
                sql.execute collateralSql, it
            }
            catch(Exception e) 
            {
                println "collateralInserts error Sql:" + collateralSql
                e.printStackTrace()
                logService.error(rootName,batch?.id,"collateralInserts 失败，SQL:"+collateralSql)

            }
        }
        sql.close()
    }

    def mortgageInserts(List<Map> estateMapList, EstateImportBatch batch)
    {
        def mortgageCategory = MortgageCategory.findByCode("1")
        def contactType = ContactType.findByCode("1")
        def contactIdentityType = ContactIdentityType.findByName('身份证')
        def evaluateStatue = ExecStatusDict.SUCCESS()

        def sql = new groovy.sql.Sql(dataSource)
        def executeSql = "select i.* from estate_import i LEFT JOIN estate e on i.external_id = e.external_id where i.evaluate_statue_id = ${evaluateStatue?.id} and e.external_id is not null and i.batch_id = ${batch?.id}"
        def result = sql.rows(executeSql)
        result?.each
        {
            try
            {
                def contactParams = [it?.account_id,it?.borr_name,it?.borr_phone,it?.borr_id_card_no,it?.borr_birthday,it?.borr_account_nature_id,it?.borr_own_house_count,it?.borr_work_unit_name,it?.borr_work_unit_nature_id,it?.borr_deposited_state_id,it?.borr_deposit_base_on_loans,it?.borr_deposit_amount_on_loans,it?.borr_deposit_base_on_current,it?.borr_deposit_amount_on_current,it?.borr_fmi_on_loans,it?.address,it?.borr_public_fund_account,contactType?.id, contactIdentityType?.id]
                def contactSql = "insert into contact(id,version,account_id,full_name,cellphone,id_number,birthday,account_nature_id,own_house_count,work_unit_name,work_unit_nature_id,deposited_state_id,deposit_base_on_loans,deposit_amount_on_loans,deposit_base_on_current,deposit_amount_on_current,fmi_on_loans,address,public_fund_account,type_id,identity_type_id,created_date,modified_date)"
                contactSql += " values(nextval('hibernate_sequence'),0,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP) "
                contactSql += " on conflict(cellphone) do nothing"
                sql.execute contactSql, contactParams

                def contact = sql.rows("select id from contact where cellphone = ${it?.borr_phone}")?.get(0)

                def params = [contact?.id, it?.account_id,it?.serial_number, batch?.id, it?.contract_no, it?.external_id, it?.create_by_id, it?.unit_price,it?.total_price,it?.ltv,it?.mortgagee_name,it?.estate_type_id,it?.account_name,it?.is_first_house,it?.down_payment_amount,it?.public_fund_loans_amount,it?.bank_loans_amount,it?.loans_bank_name,it?.star_repayment_mode_id,it?.repayment_date,it?.forward_loan_step_id,it?.loan_method_id,it?.cur_repayment_mode_id, it?.monthly_repayment_amount, it?.cur_recycle_total_capital,it?.cur_recycle_total_interest,it?.cur_recycle_total_fine,it?.cur_has_paymenttoduration,it?.cur_has_payment_duration,it?.guarantee_method_id,it?.guarantee_institution_id,it?.borr_public_fund_account,it?.borr_name,it?.borr_phone,it?.borr_id_card_no,it?.borr_birthday,it?.borr_account_nature_id,it?.borr_own_house_count,it?.borr_work_unit_name,it?.borr_work_unit_nature_id,it?.borr_deposited_state_id,it?.borr_deposit_base_on_loans,it?.borr_deposit_amount_on_loans,it?.borr_deposit_base_on_current,it?.borr_deposit_amount_on_current,it?.borr_fmi_on_loans,it?.address,mortgageCategory?.id,it?.start_date,it?.loans_evaluate_total_price,it?.loans_evaluate_price,it?.loans_amount,it?.cltv,it?.loans_balance,it?.end_date,it?.evaluate_type_id,it?.evaluate_statue_id,it?.remain_time]
                def mortgageSql = "insert into mortgage(id,version, mortgage_contact_id, account_id,serial_number,batch_id,contract_no,external_id,create_by_id,unit_price,total_price,ltv,mortgagee_name,estate_type_id,account_name,is_first_house,down_payment_amount,public_fund_loans_amount,bank_loans_amount,loans_bank_name,star_repayment_mode_id,repayment_date,forward_loan_step_id,loan_method_id,cur_repayment_mode_id,monthly_repayment_amount,cur_recycle_total_capital,cur_recycle_total_interest,cur_recycle_total_fine,cur_has_paymenttoduration,cur_has_payment_duration,guarantee_method_id,guarantee_institution_id,borr_public_fund_account,borr_name,borr_phone,borr_id_card_no,borr_birthday,borr_account_nature_id,borr_own_house_count,borr_work_unit_name,borr_work_unit_nature_id,borr_deposited_state_id,borr_deposit_base_on_loans,borr_deposit_amount_on_loans,borr_deposit_base_on_current,borr_deposit_amount_on_current,borr_fmi_on_loans,borr_address,mortgage_category_id,start_date,loans_evaluate_total_price,loans_evaluate_price,loans_amount,cltv,loans_balance,end_date,evaluate_type_id,evaluate_statue_id,remain_time,created_date, modified_date)"
                mortgageSql += " values(nextval('hibernate_sequence'),0,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP) "
                mortgageSql += " on conflict(serial_number) do nothing"
                sql.execute mortgageSql, params

                def mortgage = sql.rows("select id from mortgage where serial_number = ${it?.serial_number}")?.get(0)
                
                def contactParams1 = [mortgage?.id,contact?.id,contactType?.id]
                def contactSql1 = "INSERT INTO mortgage_contact(id, version, mortgage_id, contact_id, type_id)"
                contactSql1 += " values(nextval('hibernate_sequence'),0,?,?,?) "
                contactSql1 += " on conflict(mortgage_id, contact_id, type_id) do nothing "
                sql.execute contactSql1, contactParams1

                def collateral = sql.rows("select id from collateral where external_id = ${it?.external_id}")?.get(0)
                def contactParams2 = [mortgage?.id,collateral?.id]
                def contactSql2 = "INSERT INTO mortgage_collateral(id, version, mortgage_id, collateral_id, created_date, modified_date)"
                contactSql2 += " values(nextval('hibernate_sequence'),0,?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP) "
                contactSql2 += " on conflict(mortgage_id, collateral_id) do nothing "
                sql.execute contactSql2, contactParams2
            }
            catch(Exception e)
            {
                println "mortgageInserts error"
                e.printStackTrace()
                logService.error(rootName,batch?.id,"mortgageInserts 失败，INFO:" + it)

            }
        }
        sql.close()
    }

    def valuationHistorySuccess(EstateImportBatch batch)
    {
        def sql = new groovy.sql.Sql(dataSource)
        def executeSql = "select e.account_id, i.evaluate_code, i.community_name, i.latest_evaluate_date, i.unit_price, i.total_price, e.id, 'estate' as source_class, i.evaluate_statue_id, i.evaluate_type_id, i.fail_reason from estate_import i LEFT JOIN estate e on i.external_id = e.external_id where e.external_id is not null and i.batch_id = ${batch?.id}"
        def result = sql.rows(executeSql)
        sql.withBatch(20, "INSERT INTO valuation_history(id, version, account_id, evaluate_code, name, valuation_date, unit_price, total_price, source_id, source_class, evaluate_status_id, evaluate_type_id, fail_reason) VALUES (nextval('hibernate_sequence'),0,?1.account_id,?1.evaluate_code,?1.community_name,?1.latest_evaluate_date,?1.unit_price,?1.total_price,?1.id,?1.source_class,?1.evaluate_statue_id,?1.evaluate_type_id,?1.fail_reason)") { ps ->
            result?.each
            {
                try
                {
                    ps.addBatch(it)
                }
                catch (Exception e)
                {
                    println "valuationHistorySuccess error"
                    e.printStackTrace()
                    logService.error(rootName,batch?.id,"valuationHistorySuccess 失败，INFO:" + it)
                }
            }
        }
        sql.close()
    }

    def updateBatchTime(EstateImportBatch batchT)
    {
        def batch = EstateImportBatch.findById(batchT?.id)
        // 成功
        if (batch?.status?.code != '2')
        {
            batch.endTime = new Date()
            if (batch.validate()) 
            {
                batch.save flush: true    
            }
            else
            {
                println batch.errors
            }
        }
    }

    def estateImportSql(Map map, Long id)
    {
        println map
        def excuteSql = "update estate_import set "
        map.each
        {
            excuteSql += "${it?.key} = '${it?.value}', "
        }
        excuteSql = excuteSql?.substring(0, excuteSql.lastIndexOf(",")) + ' where id = ' + id
        println excuteSql
        return excuteSql
    }
}